package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.SearchDao;
import com.mp.approvalreq.entity.Cc;
import com.mp.approvalreq.entity.Query;
import com.mp.approvalreq.entity.Request;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

public class SearchDaoImpl extends AbstractDao implements SearchDao {

    private static final Logger logger = LoggerFactory.getLogger(SearchDaoImpl.class);

    private Connection connection;

    public SearchDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select f.Frequest_id, f.Frequest_name, f.Frequest_templet, f.Fapplicant, f.Fcreate_time,"
                + " f.Ffinished, f.Fversion, f.Fouter_position, c.Fcontent, m.Fmessage,"
                + " f.Frequest_ancestor_id, f.Frequest_content_last_total_second, f.Fnatural_content_time_last, f.Fall_judger, f.Fwho_confirm,"
                + " f.Fmanual_ending_time, f.Frela_people, f.Fchecked_time, f.Ftotal_money, f.Fcreate_time,"
                + " f.Flatest_approved_time, f.Fdisable_time, f.Ffinancial_status"
                + " from request_content c"
                + " left join request_comment m on c.Frequest_id = m.Frequest_id"
                + " left join request_flow f on f.Frequest_id = c.Frequest_id";
        return String.format("%s %s", query, condition);
    }

    @Override
    Request getObject(ResultSet rs) throws SQLException {
        Request request = new Request();
        request.setRequestDkey(rs.getInt(1));
        request.setRequestName(rs.getString(2));
        request.setTemplateId(rs.getInt(3));
        request.setApplicant(rs.getInt(4));
        request.setRequestTime(getTimeStr(rs.getTimestamp(5)));

        request.setFinished(rs.getInt(6));
        request.setVersion(rs.getInt(7));
        request.setOuterPosition(rs.getString(8));
        request.setSearchContent(rs.getString(9));
        request.setSearchMessage(rs.getString(10));

        request.setRequestAncestorId(rs.getInt(11));
        request.setRequestContentLastTotalSecond(rs.getString(12));
        request.setNaturalContentTimeLast(rs.getString(13));
        request.setAllJudger(rs.getString(14));
        request.setWhoConfirm(rs.getString(15));

        request.setManualEndingTimeStr(getTimeStr(rs.getTimestamp(16)));
        request.setManualEndingTime(rs.getTimestamp(16));

        request.setRelaPeople(rs.getString(17));
        request.setCheckedTime(rs.getString(18));
        request.setTotalMoney(rs.getString(19));
        request.setCreateTimestamp(rs.getTimestamp(20));

        request.setLatestApprovedTime(getTimeStr(rs.getTimestamp(21)));
        request.setDisableTime(getTimeStr(rs.getTimestamp(22)));
        request.setFinancialStatus(rs.getInt(23));

        return request;
    }


    @Override
    public List<Request> selectRequestApprovedByUser(Query query, List<Integer> templateIds, Set<Integer> keywordIdSet) throws SQLException {
        List<Request> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        StringBuilder condition = new StringBuilder("left join sys_approval_flow flow on f.Frequest_id = flow.request_id"
                + " where f.Fcompany_id = ? and flow.company_info_id = ? and flow.is_approved_at is not null"
                + " and f.Ffinished != -99 and f.Fis_resubmit = 0 and c.Fcontent_type != 11");
        params.add(query.getCompanyId());
        params.add(query.getApplicantCiid());

        appendSearchCondition(condition, query, params, templateIds, keywordIdSet);


        String sql = getQuerySql(condition.toString());
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (int i = 0; i < params.size(); i++) {
                Object obj = params.get(i);
                if (obj instanceof Integer) {
                    ps.setInt(i + 1, (Integer) obj);
                } else {
                    ps.setString(i + 1, (String) obj);
                }
            }
            logger.info(SQLLogger.logSQL(sql, params.toArray()));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = getObject(rs);
                    setRelevanceStr(query, request);
                    list.add(request);
                }
            }
        }
        return list;
    }

    @Override
    public List<Request> selectRequestNeedUserApprove(Query query, List<Integer> templateIds, Set<Integer> keywordIds) throws SQLException {
        List<Request> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        StringBuilder condition = new StringBuilder("left join sys_approval_flow flow on f.Frequest_id = flow.request_id"
                + " where flow.company_info_id = ? and is_approved = 0 and flow.state = 1");
        params.add(query.getCompanyInfoId());
        appendSearchCondition(condition, query, params, templateIds, keywordIds);

        String sql = getQuerySql(condition.toString());

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (int i = 0; i < params.size(); i++) {
                Object obj = params.get(i);
                if (obj instanceof Integer) {
                    ps.setInt(i + 1, (Integer) obj);
                } else {
                    ps.setString(i + 1, (String) obj);
                }
            }
            logger.info(SQLLogger.logSQL(sql, params.toArray()));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = getObject(rs);
                    setRelevanceStr(query, request);
                    list.add(request);
                }
            }
        }
        return list;
    }


    @Override
    public List<Request> selectRequestAppliedByUser(Query query, List<Integer> templateIds) throws SQLException {
        List<Request> list = new ArrayList<>();

        List<Object> params = new ArrayList<>();
        StringBuilder condition = new StringBuilder("where f.Fcompany_id = ? and (f.Fapplicant_ciid = ? or Fproxy_contact_id = ?)"
                + " and f.Ffinished != -99 and f.Fis_resubmit = 0 and c.Fcontent_type != 11");
        params.add(query.getCompanyId());
        params.add(query.getApplicantCiid());
        params.add(query.getContactId());

        appendSearchCondition(condition, query, params, templateIds, null);

        String sql = getQuerySql(condition.toString());

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (int i = 0; i < params.size(); i++) {
                Object obj = params.get(i);
                if (obj instanceof Integer) {
                    ps.setInt(i + 1, (Integer) obj);
                } else {
                    ps.setString(i + 1, (String) obj);
                }
            }
            logger.info(SQLLogger.logSQL(sql, params.toArray()));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = getObject(rs);
                    setRelevanceStr(query, request);
                    list.add(request);
                }
            }
        }
        return list;
    }

    @Override
    public List<Request> selectRequestParticipatedByUser(Query query, List<Integer> templateIds, Set<Integer> keywordIds) throws SQLException {
        List<Request> list = new ArrayList<>();

        List<Object> params = new ArrayList<>();
        StringBuilder condition = new StringBuilder("left join sys_approval_participant p on f.Frequest_id = p.request_id"
                + " where f.Fcompany_id = ? and p.company_info_id = ? and f.Ffinished != -99"
                + " and f.Fis_resubmit = 0 and c.Fcontent_type != 11");
        params.add(query.getCompanyId());
        params.add(query.getCompanyInfoId());

        appendSearchCondition(condition, query, params, templateIds, keywordIds);

        String sql = getQuerySql(condition.toString());

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (int i = 0; i < params.size(); i++) {
                Object obj = params.get(i);
                if (obj instanceof Integer) {
                    ps.setInt(i + 1, (Integer) obj);
                } else {
                    ps.setString(i + 1, (String) obj);
                }
            }
            logger.info(SQLLogger.logSQL(sql, params.toArray()));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = getObject(rs);
                    setRelevanceStr(query, request);
                    list.add(request);
                }
            }
        }
        return list;
    }


    @Override
    public List<Request> selectRequestApprovedCcToMe(Query query, List<Integer> templateIds, Set<Integer> keywordIds,
                                                     Integer filedStatus) throws SQLException {
        List<Request> list = new ArrayList<>();

        List<Object> params = new ArrayList<>();
        StringBuilder condition = new StringBuilder("left join request_filed filed on f.Frequest_id = filed.Frequest_id"
                + " where f.Fcompany_id = ? and filed.Fwho_filed_ciid = ? and filed.Ffiled_status = ? and filed.Fstatus = 0 "
                + " and f.Fis_resubmit = 0 and c.Fcontent_type != 11");
        params.add(query.getCompanyId());
        params.add(query.getCompanyInfoId());
        params.add(filedStatus);
        if (filedStatus == Cc.NEED_FILE) {
            condition.append(" and f.Ffinished = 1");
        } else {
            condition.append(" and f.Ffinished in(1,2)");
        }

        appendSearchCondition(condition, query, params, templateIds, keywordIds);

        String sql = getQuerySql(condition.toString());

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (int i = 0; i < params.size(); i++) {
                Object obj = params.get(i);
                if (obj instanceof Integer) {
                    ps.setInt(i + 1, (Integer) obj);
                } else {
                    ps.setString(i + 1, (String) obj);
                }
            }
            logger.info(SQLLogger.logSQL(sql, params.toArray()));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = getObject(rs);
                    setRelevanceStr(query, request);
                    list.add(request);
                }
            }
        }
        return list;
    }

    private void appendSearchCondition(StringBuilder condition, Query query, List<Object> params,
                                       List<Integer> templateIds, Set<Integer> keywordIdSet) {

        // 审批状态与付款状态
        if (StringUtils.isNotEmpty(query.getFinancialStatusSequence()) || StringUtils.isNotEmpty(query.getFinishedSequence())) {
            String sqlA = StringUtils.isEmpty(query.getFinishedSequence()) ? " 0 = 1" : " f.Ffinancial_status = 0 and f.Ffinished in(" + query.getFinishedSequence() + ")";
            String sqlB = StringUtils.isEmpty(query.getFinancialStatusSequence()) ? " 0 = 1 " : " f.Ffinished = 1 and f.Ffinancial_status in(" + query.getFinancialStatusSequence() + ")";
            condition.append(" and ((").append(sqlA).append(") or (").append(sqlB).append("))");
        }
        // 申请人
        if (StringUtils.isNotBlank(query.getApplicants())) {
            logger.info("指定申请人:{}", query.getApplicants());
            condition.append(" and f.Fapplicant in(").append(query.getApplicants()).append(")");
        }
        // 模版id(搜审批类型)
        if (Utils.isNotEmpty(templateIds)) {
            condition.append(" and f.Frequest_templet in(").append(Utils.collection2string(templateIds)).append(")");
        }
        // 关键词
        if (StringUtils.isNotBlank(query.getKeywords())) {
            condition.append(" and (");
            String[] keywords = query.getKeywords().trim().split(" ");
            for (String keyword : keywords) {
                logger.info("关键词:{}", keyword);

                condition.append(" (");
                // 搜组件
                condition.append("(c.Fcontent like ? and c.Fcontent_type <> 15)");
                params.add("%" + keyword + "%");

                // 搜审批名
                condition.append(" or").append(" f.Frequest_name like ?");
                params.add("%" + keyword + "%");

                // 搜评论
                condition.append(" or").append(" (m.Fmessage like ? and m.Fis_attach != 1)");
                params.add("%" + keyword + "%");

                // 搜审批编号（全字匹配）
                condition.append(" or").append(" f.Fapproval_num = ?");
                params.add(keyword);

                // 搜审批编号（模糊匹配,尾号）
                condition.append(" or").append(" f.Fapproval_num like ?");
                params.add("%" + keyword);

                condition.append(")");

                condition.append(" or");
            }
            if (Utils.isNotEmpty(keywordIdSet)) {
                for (Integer companyInfoId : keywordIdSet) {
                    condition.append(" f.Fapplicant_ciid = ?");
                    condition.append(" or");
                    params.add(companyInfoId);
                }
            }

            condition.delete(condition.length() - 2, condition.length());
            condition.append(")");
        }
        // 开始时间
        if (StringUtils.isNotBlank(query.getStartDate())) {
            condition.append(" and date_format(f.Fcreate_time,'%Y-%m-%d') >= ?");
            params.add(query.getStartDate());
        }
        // 结束时间
        if (StringUtils.isNotBlank(query.getEndDate())) {
            condition.append(" and date_format(f.Fcreate_time,'%Y-%m-%d') <= ?");
            params.add(query.getEndDate());
        }
        // 去重
        condition.append(" group by f.Frequest_id");
        // 排序
        condition.append(" order by");
        if (StringUtils.isNotBlank(query.getOrderBy())) {
            switch (query.getOrderBy()) {
                case "0":
                    // 智能排序,按审批人批准/驳回时间
                    condition.append(" flow.is_approved_at desc");
                    break;
                case "1":
                    // 创建时间
                    condition.append(" f.Fcreate_time desc");
                    break;
                case "2":
                    // 最后批准时间
                    condition.append(" f.Ffinally_confirmed_time desc");
                    break;
                case "3":
                    // 归档时间
                    condition.append(" f.Ffinally_cc_time desc");
                    break;
                default:
                    break;
            }
        } else {
            condition.append(" f.Fcreate_time desc");
        }
    }


    private void setRelevanceStr(Query query, Request request) {
        if (StringUtils.isNotBlank(query.getKeywords())) {
            if (StringUtils.isNotBlank(request.getSearchContent())) {
                request.setRelevanceStr(isContainKeyWords(request.getSearchContent(), query.getKeywords()) ?
                        request.getSearchContent() : null);
            }
            if (StringUtils.isBlank(request.getRelevanceStr())) {
                if (StringUtils.isNotBlank(request.getSearchMessage())) {
                    request.setRelevanceStr(isContainKeyWords(request.getSearchMessage(), query.getKeywords()) ?
                            request.getSearchMessage() : request.getRequestName());
                }
            }
            if (StringUtils.isBlank(request.getRelevanceStr())) {
                request.setRelevanceStr(request.getRequestName());
            }
        }
    }

    private boolean isContainKeyWords(String target, String keyWords) {
        String[] split = keyWords.trim().split(" ");
        for (String s : split) {

            if (target.contains(s) && StringUtils.isNotBlank(s)) {
                return true;
            }
        }
        return false;
    }

}
